package org.ssp.test;

import org.ssp.util.DBUtil;

public class CreatTables {

	public static void main(String[] args) {
		//创建user表
	  String sql1="create table user("
	  		+ "uid char(50) not null primary key,"
	  		+ "phone char(11) ,"
	  		+ "password varchar(50),"
	  		+ "email varchar(30),"
	  		+ "school varchar(40),"
	  		+ "grade int(1),"
	  		+ "due date,"
	  		+ "openid varchar(50),"
	  		+ "type int(1) default 0,"
	  		+ "assistid varchar(20),"
	  		+ "name varchar(20),"
	  		+ "img text,"
	  		+ "nickname varchar(20),"
	  		+ "major varchar(100),"
	  		+ "address varchar(100),"
	  		+ "constraint fk foreign key(assistid) references assist(id)"
	  		+ ");";
	  
	  String sql2="create table travel("
	  		+ "id int(10) primary key not null auto_increment,"
	  		+ "title varchar(255),"
	  		+ "content text,"
	  		+ "counter int(10),"
	  		+ "datetime date,"
	  		+ "firstimgurl varchar(100),"
	  		+ "money double(9,2)"
	  		+ "); ";
	  String sql3="create table news("
		  		+ "id int(10) primary key not null auto_increment,"
		  		+ "title varchar(255),"
		  		+ "content text,"
		  		+ "counter int(10),"
		  		+ "datetime date,"
		  		+ "firstimgurl varchar(100)"
		  		+ "); ";
	  String sql4="create table assist("
	  		+ "id varchar(20) primary key,"
	  		+ "name varchar(20),"
	  		+ "department varchar(50),"
	  		+ "phone char(11),"
	  		+ "email varchar(30),"
	  		+ "wxid varchar(100),"
	  		+ "wxcode varchar(100),"
	  		+ "type int(1),"
	  		+ "password varchar(20)"//保存图片路径
	  		+ ");";
	  String sql5="create table task("
	  		+ "id int(20) primary key auto_increment not null,"
	  		+ "name varchar(100),"
	  		+ "content text,"
	  		+ "due date,"
	  		+ "assistid varchar(20),"
	  		+ "parentid int(20),"
	  		+ "status int(1),"
	  		+ "attachmenturl text,"
	  		+ "responsibility varchar(20),"
	  		+ "foreign key(parentid) references task(id) on delete cascade on update cascade,"
	  		+ "foreign key(assistid) references assist(id) on delete cascade on update cascade,"
	  		+ "foreign key(responsibility) references assist(id) on delete cascade on update cascade"
	  		+ ");";
	  String sql6="create table backupforuser("
	  		+ "id int(50) primary key auto_increment,"
	  		+ "content text,"
	  		+ "datetime varchar(30),"
	  		+ "uid varchar(50),"
	  		+ "assistid varchar(20),"
	  		+ "isread int(1) default 0,"
	  		+ "availableopen int(1) default 0,"
	  		+ "foreign key(assistid) references assist(id) on delete cascade on update cascade,"
	  		+ "foreign key(uid) references user(uid) on delete cascade on update cascade"
	  		+ ");";
	  String sql7="create table user_travel("
	  		+ "uid char(50),"
	  		+ "travelid int(10),"
	  		+ "primary key(uid,travelid),"
	  		+ "foreign key(uid) references user(uid) on delete cascade on update cascade,"
	  		+ "foreign key(travelid) references travel(id) on delete cascade on update cascade"
	  		+ ");";
	  String sql8="create table backupfortravel("
	  		+ "id int(50) primary key auto_increment,"
	  		+ "content text,"
	  		+ "datetime varchar(30),"
	  		+ "travelid int(10),"
	  		+ "assistid varchar(20),"
	  		+ "foreign key(travelid) references travel(id) on delete cascade on update cascade,"
	  		+ "foreign key(assistid) references assist(id) on delete cascade on update cascade"
	  		+ ");";
	  String sql9="create table school("
	  		+ "id int(20) primary key auto_increment,"
	  		+ "name  varchar(20),"
	  		+ "content text,"
	  		+ "rank int(50)"
	  		+ ");";
	  String sql10="create table midschool("
		  		+ "id int(20) primary key auto_increment,"
		  		+ "name  varchar(20),"
		  		+ "content text"
		  		+ ");";
	  String sql11="create table applystep("
	  		+ "id int(30) primary key auto_increment,"
	  		+ "content text,"
	  		+ "dattime date"
	  		+ ");";
	  String sql12="create table visa("
		  		+ "id int(30) primary key auto_increment,"
		  		+ "content text,"
		  		+ "dattime date"
		  		+ ");";
	  String sql13="create table partjob("
		  		+ "id int(30) primary key auto_increment,"
		  		+ "title varchar(50),"
		  		+ "content text,"
		  		+ "datetime date"
		  		+ ");";
	  String sql14="create table study("
		  		+ "id int(30) primary key auto_increment,"
		  		+ "title varchar(50),"
		  		+ "content text,"
		  		+ "datetime date"
		  		+ ");";
	  String sql15="create table task_joins("
	  		+ "taskid int(20),"
	  		+ "assistid varchar(20),"
	  		+ "primary key(taskid,assistid),"
	  		+ "foreign key(taskid) references task(id) on delete cascade on update cascade,"
	  		+ "foreign key(assistid) references assist(id) on delete cascade on update cascade"
	  		+ ");";
	  String sql16="create table task_user("
	  		+ "taskid int(20),"
	  		+ "userid  char(50),"
	  		+ "primary key(taskid,userid),"
	  		+ "foreign key(taskid) references task(id) on delete cascade on update cascade,"
	  		+ "foreign key(userid) references user(uid) on delete cascade on update cascade"
	  		+ ");";
	  String sql17="create table task_attachmenturl("
	  		+ "id int(20) primary key auto_increment,"
	  		+ "taskid int(20),"
	  		+ "url varchar(100),"
	  		+ "title varchar(100),"
	  		+ "foreign key(taskid) references task(id) on delete cascade on update cascade"
	  		+ ");";
	  String sql18="create table task_travel("
	  		+ "taskid int(20),"
	  		+ "travelid int(10),"
	  		+ "primary key(taskid,travelid),"
	  		+ "foreign key(taskid) references task(id) on delete cascade on update cascade,"
	  		+ "foreign key(travelid) references travel(id) on delete cascade on update cascade"
	  		+ ");";
	  String sql19="create table user_files("
	  		+ "id int(10)  primary key auto_increment,"
	  		+ "uid char(50),"
	  		+ "url varchar(100),"
	  		+ "title varchar(100),"
	  		+ "foreign key(uid) references user(uid) on delete cascade on update cascade"
	  		+ ");";
	  String sql20="create table user_url("
	  		+ "uid varchar(50),"
	  		+ "url varchar(100),"
	  		+ "primary key (uid,url),"
	  		+ "foreign key(uid) references user(uid) on delete cascade on update cascade"
	  		+ ");";
	  String sql21="create table school_major("
	  		+ "schoolid int(20),"
	  		+ "fieldname varchar(20),"
	  		+ "filevalue varchar(10),"
	  		+ "foreign key(schoolid) references school(id) on delete cascade"
	  		+ ");";
	  String sql22="create table major("
	  		+ "id int(20) primary key auto_increment,"
	  		+ "name varchar(20)"
	  		+ ");";
	  String sql23="create table ordertab("
	  		+ "orderid  char(16) not null primary key,"
	  		+ "name varchar(50),"
	  		+ "paytime date,"
	  		+ "openid varchar(50),"
	  		+ "status int(1),"
	  		+ "total_fee int(10)"
	  		+ ");";
	  /*DBUtil.createTable(sql2);
	  DBUtil.createTable(sql3);
	  DBUtil.createTable(sql4);
	  DBUtil.createTable(sql9);
	  DBUtil.createTable(sql1);
	  DBUtil.createTable(sql5);
	  DBUtil.createTable(sql6);
	  DBUtil.createTable(sql7);
	  DBUtil.createTable(sql8);*/
	  /*DBUtil.createTable(sql5);
	  DBUtil.createTable(sql15);*/
	  DBUtil.createTable(sql23);
	}
}
